ALTER  PROCEDURE [dbo].[psMailTotalHandleState] (@fpRole varchar(10),@UnitId varchar(30))
AS
BEGIN
set nocount on --关闭计数

--临时表
declare @tmpTab table (tRow int identity(1,1),wCount int,menuNo int,clewDesc varchar(400))

-- 初始化入参
declare @deptId varchar(30)
if len(@UnitId) <> 0
	select @deptId = @UnitId
else
	select @deptId = '-1'

-- 变量定义 vs @tmpTab {
declare @pwCount int,@tmpInt int,@pclewDesc varchar(400)
declare @curDate date,@startDate varchar(10),@endDate varchar(10)
declare @curYear int,@curMonth int,@curDay int
declare @menuName varchar(50),@menuNo int
-- 初始化值
set @curDate = GETDATE()
set @curYear = Year(@curDate)
set @curMonth = Month(@curDate)
set @curDay = Day(@curDate)

-- 超过25号后起始时间变为本月25号到下月26号
--set @startDate = IIF(@curDay >= 25,CONCAT(@curYear,'-',@curMonth,'-25'),CONCAT(@curYear,'-',@curMonth-1,'-25'))
--set @endDate = IIF(@curDay >= 25,CONCAT(@curYear,'-',@curMonth+1,'-26'),CONCAT(@curYear,'-',@curMonth,'-26'))
if @curDay >= 25
begin
set @startDate = convert(char(10),DATEADD(dd,-DAY(GETDATE())+25,GETDATE()),120)+' 00:00:00.000'
set @endDate = convert(char(10),DATEADD(mm,1,DATEADD(dd,-DAY(GETDATE())+26,GETDATE())),120)+' 23:59:59.999'
end
else
begin
set @startDate = convert(char(10),DATEADD(mm,-1,DATEADD(dd,-DAY(GETDATE())+25,GETDATE())),120)+' 00:00:00.000'
set @endDate = convert(char(10),DATEADD(dd,-DAY(GETDATE())+26,GETDATE()),120)+' 23:59:59.999'
end

-- 不满意量、平均回复周期
DECLARE @dislikeCount int
DECLARE @replyAvgPeriod DECIMAL(38,2)
DECLARE @dept VARCHAR ( 50 )
SET @dept = '304'
SELECT @dislikeCount=SUM ( bmy ),
	@replyAvgPeriod=CONVERT (
		VARCHAR,
		CAST ( CONVERT ( FLOAT, SUM ( wk ) ) / CONVERT ( FLOAT, SUM ( bj ) ) AS DECIMAL ( 38, 2 ) )
	)
FROM
	(
	SELECT
		bd.DeptShort,
		COUNT ( twf.PkId ) pkid,
		COUNT ( bj.pkid ) bj,
		COUNT ( bmy.WfId ) AS bmy,
		SUM ( wk.cn ) wk
	FROM
		(
		SELECT
			c.SBDeptID,
			c.SBDeptName,
			c.PkId,
			b.PkId wfid
		FROM
			tTelGetDeal a,
			tWorkFormBase b,
			tWfSendBack c
		WHERE
			a.PkId= b.GetDealPK
			AND b.PkId= c.WfId
			AND b.GetDealPK= c.GetDealPK
			AND a.IsDel= 0
			AND b.IsDel= 0
			AND c.IsDel= 0
			AND b.crtstepid<>- 1
			AND c.SendState NOT IN ( 30, 40 )
			AND c.SDeptID =@dept
			AND a.FromTime>=@startDate
			AND a.FromTime<=@endDate
			AND c.sbdeptid=@deptId
		) twf
		LEFT JOIN ( SELECT pkid FROM tWfSendBack WHERE BackState > 10 AND BackTime IS NOT NULL AND sendstate NOT IN ( 30, 40 ) AND isdel = 0 ) bj ON twf.PkId= bj.PkId
		LEFT JOIN (
		SELECT
			wfid
		FROM
			tWfJudge x
		WHERE
			x.PkId= ( SELECT MAX ( pkid ) FROM tWfJudge y WHERE x.WfId= y.WfId AND y.IsDel= 0 )
			AND IsDel = 0
			AND JudgeC = 1
		GROUP BY
			wfid
		) bmy ON twf.wfid= bmy.wfid
		LEFT JOIN (
		SELECT
			b.PkId,
			COUNT ( iswork ) cn
		FROM
			[bWorkDay] a,
			tWfSendBack b
		WHERE
			RtnStr >= SendTime
			AND natureday <= BackTime AND IsWork = 1 AND b.IsDel= 0 AND SDeptID =@dept AND BackState > 10
			AND b.SendState NOT IN ( '30', '40' )
		GROUP BY
			b.pkid
		) wk ON twf.PkId= wk.pkid
		LEFT JOIN bUserDept bd ON twf.SBDeptID= bd.NoUnit
	GROUP BY
		bd.DeptShort UNION ALL
	SELECT
		bd.DeptShort,
		COUNT ( twf.PkId ) pkid,
		COUNT ( bj.pkid ) bj,
		COUNT ( bmy.WfId ) bmy,
		SUM ( wk.cn ) wk
	FROM
		(
		SELECT
			c.SBDeptID,
			c.SBDeptName,
			c.PkId,
			b.PkId wfid
		FROM
			mMailGetDeal a,
			mMailWorkForm b,
			mWfSendBack c
		WHERE
			a.PkId= b.OriginFK
			AND b.PkId= c.WfId
			AND a.IsDel= 0
			AND b.IsDel= 0
			AND c.IsDel= 0
			AND b.crtstepid<>- 1
			AND c.SendState NOT IN ( 30, 40 )
			AND c.SDeptID =@dept
			AND c.sbdeptid=@deptId
			AND a.FromTime>=@startDate
			AND a.FromTime<=@endDate
		) twf
		LEFT JOIN ( SELECT pkid FROM mWfSendBack WHERE BackState > 10 AND BackTime IS NOT NULL AND sendstate NOT IN ( 30, 40 ) AND isdel = 0 ) bj ON twf.PkId= bj.PkId
		LEFT JOIN (
		SELECT
			wfid
		FROM
			mWfJudge x
		WHERE
			x.PkId= ( SELECT MAX ( pkid ) FROM mWfJudge y WHERE x.WfId= y.WfId AND y.IsDel= 0 )
			AND IsDel = 0
			AND JudgeC = 1
		GROUP BY
			wfid
		) bmy ON twf.wfid= bmy.wfid
		LEFT JOIN (
		SELECT
			b.PkId,
			COUNT ( iswork ) cn
		FROM
			[bWorkDay] a,
			mWfSendBack b
		WHERE
			RtnStr >= SendTime
			AND natureday <= BackTime AND IsWork = 1 AND b.IsDel= 0 AND SDeptID =@dept AND BackState > 10
			AND b.SendState NOT IN ( '30', '40' )
		GROUP BY
			b.pkid
		) wk ON twf.PkId= wk.pkid
		LEFT JOIN bUserDept bd ON twf.SBDeptID= bd.NoUnit
	GROUP BY
		bd.DeptShort UNION ALL
	SELECT
		bd.DeptShort,
		COUNT ( twf.PkId ) pkid,
		COUNT ( bj.pkid ) bj,
		COUNT ( bmy.WfId ) bmy,
		SUM ( wk.cn ) wk
	FROM
		(
		SELECT
			c.SBDeptID,
			c.SBDeptName,
			c.PkId,
			b.PkId wfid
		FROM
			pMsgGetDeal a,
			pMsgWorkForm b,
			pWfSendBack c
		WHERE
			a.PkId= b.OriginFK
			AND b.PkId= c.WfId
			AND a.IsDel= 0
			AND b.IsDel= 0
			AND c.IsDel= 0
			AND b.crtstepid<>- 1
			AND c.SendState NOT IN ( 30, 40 )
			AND c.SDeptID =@dept
			AND c.sbdeptid=@deptId
			AND a.FromTime>=@startDate
			AND a.FromTime<=@endDate
		) twf
		LEFT JOIN ( SELECT pkid FROM pWfSendBack WHERE BackState > 10 AND BackTime IS NOT NULL AND sendstate NOT IN ( 30, 40 ) AND isdel = 0 ) bj ON twf.PkId= bj.PkId
		LEFT JOIN (
		SELECT
			wfid
		FROM
			pWfJudge x
		WHERE
			x.PkId= ( SELECT MAX ( pkid ) FROM pWfJudge y WHERE x.WfId= y.WfId AND y.IsDel= 0 )
			AND IsDel = 0
			AND JudgeC = 1
		GROUP BY
			wfid
		) bmy ON twf.wfid= bmy.wfid
		LEFT JOIN (
		SELECT
			b.PkId,
			COUNT ( iswork ) cn
		FROM
			[bWorkDay] a,
			pWfSendBack b
		WHERE
			RtnStr >= SendTime
			AND natureday <= BackTime AND IsWork = 1 AND b.IsDel= 0 AND SDeptID =@dept AND BackState > 10
			AND b.SendState NOT IN ( '30', '40' )
		GROUP BY
			b.pkid
		) wk ON twf.PkId= wk.pkid
		LEFT JOIN bUserDept bd ON twf.SBDeptID= bd.NoUnit
	GROUP BY
		bd.DeptShort
	) xx
GROUP BY
	xx.DeptShort
ORDER BY
	SUM ( xx.pkid ) DESC

--平均回复周期(无时间限制)
DECLARE @replyAvgPeriodUn DECIMAL(38,2)
SELECT
	@replyAvgPeriodUn=CONVERT (
		VARCHAR,
		CAST ( CONVERT ( FLOAT, SUM ( wk ) ) / CONVERT ( FLOAT, SUM ( bj ) ) AS DECIMAL ( 38, 2 ) )
	)
FROM
	(
	SELECT
		bd.DeptShort,
		COUNT ( twf.PkId ) pkid,
		COUNT ( bj.pkid ) bj,
		SUM ( wk.cn ) wk
	FROM
		(
		SELECT
			c.SBDeptID,
			c.SBDeptName,
			c.PkId,
			b.PkId wfid
		FROM
			tTelGetDeal a,
			tWorkFormBase b,
			tWfSendBack c
		WHERE
			a.PkId= b.GetDealPK
			AND b.PkId= c.WfId
			AND b.GetDealPK= c.GetDealPK
			AND a.IsDel= 0
			AND b.IsDel= 0
			AND c.IsDel= 0
			AND b.crtstepid<>- 1
			AND c.SendState NOT IN ( 30, 40 )
			AND c.SDeptID =@dept
			AND c.sbdeptid=@deptId
		) twf
		LEFT JOIN ( SELECT pkid FROM tWfSendBack WHERE BackState > 10 AND BackTime IS NOT NULL AND sendstate NOT IN ( 30, 40 ) AND isdel = 0 ) bj ON twf.PkId= bj.PkId
		LEFT JOIN (
		SELECT
			b.PkId,
			COUNT ( iswork ) cn
		FROM
			[bWorkDay] a,
			tWfSendBack b
		WHERE
			RtnStr >= SendTime
			AND natureday <= BackTime AND IsWork = 1 AND b.IsDel= 0 AND SDeptID =@dept AND BackState > 10
			AND b.SendState NOT IN ( '30', '40' )
		GROUP BY
			b.pkid
		) wk ON twf.PkId= wk.pkid
		LEFT JOIN bUserDept bd ON twf.SBDeptID= bd.NoUnit
	GROUP BY
		bd.DeptShort UNION ALL
	SELECT
		bd.DeptShort,
		COUNT ( twf.PkId ) pkid,
		COUNT ( bj.pkid ) bj,
		SUM ( wk.cn ) wk
	FROM
		(
		SELECT
			c.SBDeptID,
			c.SBDeptName,
			c.PkId,
			b.PkId wfid
		FROM
			mMailGetDeal a,
			mMailWorkForm b,
			mWfSendBack c
		WHERE
			a.PkId= b.OriginFK
			AND b.PkId= c.WfId
			AND a.IsDel= 0
			AND b.IsDel= 0
			AND c.IsDel= 0
			AND b.crtstepid<>- 1
			AND c.SendState NOT IN ( 30, 40 )
			AND c.SDeptID =@dept
			AND c.sbdeptid=@deptId
		) twf
		LEFT JOIN ( SELECT pkid FROM mWfSendBack WHERE BackState > 10 AND BackTime IS NOT NULL AND sendstate NOT IN ( 30, 40 ) AND isdel = 0 ) bj ON twf.PkId= bj.PkId
		LEFT JOIN (
		SELECT
			b.PkId,
			COUNT ( iswork ) cn
		FROM
			[bWorkDay] a,
			mWfSendBack b
		WHERE
			RtnStr >= SendTime
			AND natureday <= BackTime AND IsWork = 1 AND b.IsDel= 0 AND SDeptID =@dept AND BackState > 10
			AND b.SendState NOT IN ( '30', '40' )
		GROUP BY
			b.pkid
		) wk ON twf.PkId= wk.pkid
		LEFT JOIN bUserDept bd ON twf.SBDeptID= bd.NoUnit
	GROUP BY
		bd.DeptShort UNION ALL
	SELECT
		bd.DeptShort,
		COUNT ( twf.PkId ) pkid,
		COUNT ( bj.pkid ) bj,
		SUM ( wk.cn ) wk
	FROM
		(
		SELECT
			c.SBDeptID,
			c.SBDeptName,
			c.PkId,
			b.PkId wfid
		FROM
			pMsgGetDeal a,
			pMsgWorkForm b,
			pWfSendBack c
		WHERE
			a.PkId= b.OriginFK
			AND b.PkId= c.WfId
			AND a.IsDel= 0
			AND b.IsDel= 0
			AND c.IsDel= 0
			AND b.crtstepid<>- 1
			AND c.SendState NOT IN ( 30, 40 )
			AND c.SDeptID =@dept
			AND c.sbdeptid=@deptId
		) twf
		LEFT JOIN ( SELECT pkid FROM pWfSendBack WHERE BackState > 10 AND BackTime IS NOT NULL AND sendstate NOT IN ( 30, 40 ) AND isdel = 0 ) bj ON twf.PkId= bj.PkId
		LEFT JOIN (
		SELECT
			b.PkId,
			COUNT ( iswork ) cn
		FROM
			[bWorkDay] a,
			pWfSendBack b
		WHERE
			RtnStr >= SendTime
			AND natureday <= BackTime AND IsWork = 1 AND b.IsDel= 0 AND SDeptID =@dept AND BackState > 10
			AND b.SendState NOT IN ( '30', '40' )
		GROUP BY
			b.pkid
		) wk ON twf.PkId= wk.pkid
		LEFT JOIN bUserDept bd ON twf.SBDeptID= bd.NoUnit
	GROUP BY
		bd.DeptShort
	) xx
GROUP BY
	xx.DeptShort
ORDER BY
	SUM ( xx.pkid ) DESC

--市级&区县ZF
IF CHARINDEX(@fpRole, '210,240,260,290,310,340') > 0
BEGIN
	SELECT @menuName = '全区实时满意度情况'
	-- 办理量
	select @tmpInt=count(1) FROM [mWfSendBack] WHERE SendTime BETWEEN @startDate AND @endDate AND SBDeptID = @deptId AND BackState in (11,21) AND IsDel = 0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
	if @pwCount<>0
     select @pclewDesc='办理量：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='办理量：<span> 0 </span> 件'
	set @menuNo = 3359
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNo,@pclewDesc)
	-- 不满意量
	if @dislikeCount is null
		 select @pwCount = 0
  else
		 select @pwCount = @dislikeCount
  select @pclewDesc='不满意量：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  INSERT into @tmpTab(wCount,clewDesc) VALUES(@pwCount,@pclewDesc)
	-- 平均回复周期
	if @replyAvgPeriodUn is null
		 select @pwCount = 0
  else
		 select @pwCount = @replyAvgPeriodUn
  select @pclewDesc='平均回复周期：<span>'+cast(@pwCount as varchar(20))+'</span> 时'
-- 	if @pwCount<>0
--      select @pclewDesc='平均回复周期：<span>'+cast(@pwCount as varchar(20))+'</span> 时'
--   else
--      select @pclewDesc='平均回复周期：<span>0</span> 时'
  INSERT into @tmpTab(wCount,clewDesc) VALUES(@pwCount,@pclewDesc)
	-- 未转派工单
	select @tmpInt=count(1) FROM [mWfSendBack] WHERE  SBDeptID = @deptId
	AND SendState=22 and IsDel=0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='未转派工单：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='未转派工单：<span> 0 </span> 件'
	set @menuNo = 3343
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNO,@pclewDesc)
	-- 被市级拒退工单
	select @tmpInt=count(1) from [mWfSendBack] where  SBDeptID = @deptId
	And SendState=51 and dbo.frMailWFStep(WfId)>399 and IsDel=0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='被市级拒退工单：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='被市级拒退工单：<span> 0 </span> 件'
	set @menuNo = 3342
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNO,@pclewDesc)
	-- 临期工单
	select @tmpInt=count(1) from [mWfSendBack] where  SBDeptID = @deptId
	AND SendState in(21,23) and BackState in (0,12,22) and IsDel=0
	AND DATEDIFF(day, GETDATE(), BackLimit) IN (1,0)
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='临期工单：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='临期工单：<span> 0 </span> 件'
  set @menuNo = 3301
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNO,@pclewDesc)
	-- 超期工单
	select @tmpInt=count(1) from [mWfSendBack] where SBDeptID = @deptId
	AND SendState in(21,23) and BackState in (0,12,22) AND GETDATE() > BackLimit and IsDel=0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='超期工单：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='超期工单：<span> 0 </span> 件'
  set @menuNo = 3302
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNO,@pclewDesc)
	-- 承办单位未签收工单
	select @tmpInt=count(1) from [mWfSendBack] where  SDeptID=@deptId
	and SendState in (10,11) and dbo.frMailWFStep(WfId) > 399 and IsDel=0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='承办单位未签收工单：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='承办单位未签收工单：<span> 0 </span> 件'
  set @menuNo = 3323
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNO,@pclewDesc)
	-- 承办单位已退工单
	select @tmpInt=count(1) from [mWfSendBack] where  SDeptID=@deptId
	And SendState=40 AND BackState=21 and IsDel=0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='承办单位已退工单：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='承办单位已退工单：<span> 0 </span> 件'
	set @menuNo = 3369
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNO,@pclewDesc)
END

-- 区县部门
IF CHARINDEX(@fpRole, '360,390')> 0
BEGIN
	SELECT @menuName = '本月实时满意度情况'
	-- 办理量
	select @tmpInt=count(1) FROM [mWfSendBack] WHERE SendTime BETWEEN @startDate AND @endDate AND SBDeptID = @deptId AND BackState in (11,21) AND IsDel = 0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='办理量：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='办理量：<span> 0 </span> 件'
  set @menuNo = 3359
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNo,@pclewDesc)
	-- 不满意量
	if @dislikeCount is null
		 select @pwCount = 0
  else
		 select @pwCount = @dislikeCount
  select @pclewDesc='不满意量：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  INSERT into @tmpTab(wCount,clewDesc) VALUES(@pwCount,@pclewDesc)
	-- 平均回复周期
	if @replyAvgPeriodUn is null
		 select @pwCount = 0
  else
		 select @pwCount = @replyAvgPeriodUn
  select @pclewDesc='平均回复周期：<span>'+cast(@pwCount as varchar(20))+'</span> 时'
  INSERT into @tmpTab(wCount,clewDesc) VALUES(@pwCount,@pclewDesc)
	-- 未签收工单
	select @tmpInt=count(1) FROM [mWfSendBack] WHERE  SBDeptID = @deptId AND SendState IN (10, 11)  and dbo.frMailWFStep(WfId) > 399 AND IsDel = 0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='未签收工单：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='未签收工单：<span> 0 </span> 件'
  set @menuNo = 3363
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNo,@pclewDesc)
	-- 已退工单
	select @tmpInt=count(1) from [mWfSendBack] where  SBDeptID = @deptId AND SendState= 40 and IsDel=0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='已退工单：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='已退工单：<span> 0 </span> 件'
  set @menuNo = 3365
  INSERT into @tmpTab(wCount,menuNo,clewDesc) VALUES(@pwCount,@menuNo,@pclewDesc)
	-- 未向三级单位下派工单
	select @tmpInt=count(1) from [mWfSendBack] where  SBDeptID = @deptId AND SendState in(21,23)
	and BackState in (0,12,22) AND GETDATE() > BackLimit and IsDel=0
  if @tmpInt is null
     select @pwCount=0
  else
     select @pwCount=@tmpInt
  if @pwCount<>0
     select @pclewDesc='未向三级单位下派工单：<span>'+cast(@pwCount as varchar(5))+'</span> 件'
  else
     select @pclewDesc='未向三级单位下派工单：<span> 0 </span> 件'
  INSERT into @tmpTab(wCount,clewDesc) VALUES(@pwCount,@pclewDesc)
END

SELECT @menuName as menuName,menuNo,wCount,clewDesc FROM @tmpTab

set nocount off--打开计数

END